﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using MxTol.Managers.Helpers;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;

namespace MxTol.Managers.Helpers
{
    /// <summary>
    /// 数据库分页基类，使用该类请确保数据库中存有分页存储过程。
    /// </summary>
    internal class PaginationBase
    {
        private string m_connectionStr;     // 连接数据库字符串
        private string m_procedureName;     // 存储过程名称

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return m_connectionStr; }
            set { m_connectionStr = value; }
        }

        /// <summary>
        /// 分页存储过程名称
        /// </summary>
        public string ProcedureName
        {
            get { return m_procedureName; }
            set { m_procedureName = value; }
        }

        public PaginationBase() { }

        public PaginationBase(string connectionStr, string procedureName)
        {
            this.m_connectionStr = connectionStr;
            this.m_procedureName = procedureName;
        }

        /// <summary>
        /// 获取分页原始数据
        /// </summary>
        /// <param name="sql">需要查询的SQL语句</param>
        /// <param name="pageSize">页面记录大小</param>
        /// <param name="pageIndex">当前页面</param>
        private DataSet GetDataSet(string sql, int pageSize, int pageIndex)
        {
            DataSet dataSet = new DataSet();

            using (SqlConnection connection = new SqlConnection(m_connectionStr))
            using (SqlCommand cmd = new SqlCommand(m_procedureName, connection))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@sqlQuery", SqlDbType.NVarChar);
                cmd.Parameters.Add("@pageSize", SqlDbType.Int);
                cmd.Parameters.Add("@pageIndex", SqlDbType.Int);

                cmd.Parameters["@sqlQuery"].Value = sql;
                cmd.Parameters["@pageSize"].Value = pageSize;
                cmd.Parameters["@pageIndex"].Value = pageIndex;

                try
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dataSet);
                }
                catch (System.Exception ex)
                {
                    throw new Exception(sql + ex.Message);
                }
            }
            return dataSet;
        }

        /// <summary>
        /// 对分页存储过程返回的数据进行分页处理
        /// </summary>
        /// <param name="dataSet">分页存储过程返回的DataSet的数据</param>
        /// <param name="currentPage">当前页码</param>
        /// <param name="pagesize">页面记录大小</param>
        /// <param name="totalPageCount">最大页数</param>
        /// <param name="totalCount">总记录数</param>
        private DataTable GetDataTable(DataSet dataSet, int currentPage, int pagesize, out int totalPageCount, out int totalCount)
        {
            if (dataSet == null)
            {
                throw new ArgumentException("the source of dataset is null .");
            }

            if (dataSet.Tables.Count == 4)
            {
                // 总记录条数
                totalCount = dataSet.Tables[2].Rows[0][0] is DBNull ? 0 : Convert.ToInt32(dataSet.Tables[2].Rows[0][0]);

                // 总页数
                totalPageCount = totalCount % pagesize > 0 ? totalCount / pagesize + 1 : totalCount / pagesize;

                return dataSet.Tables[3];
            }
            else
            {
                throw new Exception("查询数据出现异常。");
            }
        }

        /// <summary>
        /// 根据查询语句获取符合EasyUI表格控件所需格式的json数据
        /// </summary>
        /// <param name="currentPage">当前页面索引</param>
        /// <param name="pageSize">返回页面记录条数</param>
        /// <param name="sql">查询语句</param>
        public string GetJsonBySql(int currentPage, int pageSize, string sql)
        {
            string json = string.Empty;

            try
            {
                int pageCount = 0;
                int recordCount = 0;

                DataSet dataSet = GetDataSet(sql, pageSize, currentPage);
                DataTable table = GetDataTable(dataSet, currentPage, pageSize, out pageCount, out recordCount);

                Dictionary<string, object> dic = new Dictionary<string, object>()
                {
                    {"rows",table},
                    {"total",recordCount},
                    {"Pagesize", pageSize},
                    {"Pagecount", pageCount},
                    {"Currpage", currentPage}
                };

                IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
                json = JsonConvert.SerializeObject(dic, Formatting.Indented, timeConverter);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return json;
        }

        /// <summary>
        /// 根据查询语句获取所有json数据,该方法不对数据进行分页处理
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetJsonBySql(string sql)
        {
            string json = string.Empty;

            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(m_connectionStr, sql);
                if (dataSet != null && dataSet.Tables.Count > 0)
                {
                    DataTable table = dataSet.Tables[0];

                    IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
                    timeConverter.DateTimeFormat = "yyyy-MM-dd";
                    json = JsonConvert.SerializeObject(table, Formatting.Indented, timeConverter);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return json;
        }
    }
}